/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import entity.Assignment;
import entity.Entity_Class;
import entity.Mark;
import entity.Staff;
import entity.Student;
import entity.SubmitOfAssignment;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ZERO
 */
public class DataProcess {
    public Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://ZER0;databaseName=MAILDATA";
            String user = "sa";
            String pass = "lam123456";
            conn = DriverManager.getConnection(url, user, pass);
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conn;
    }

    public  Staff getStaffByEmailName(String emailname) {
        Staff s = null;
        String sql = "SELECT * FROM tblStaff WHERE _emailName=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, emailname);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    s = new Staff(rs.getString("_staffID"), rs.getString("_emailName"), rs.getString("_firstName"),
                            rs.getString("_lastName"), rs.getString("_qualification"), rs.getString("_address"),
                            rs.getString("_phone"), rs.getDate("_birthDay"), rs.getString("_gender"));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return s;
    }

    public  Vector<Entity_Class> getClassByStaffID(String staffid) {
        Vector<Entity_Class> v = new Vector<Entity_Class>();
        String sql = "SELECT * FROM tblClass WHERE _staffID=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, staffid);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    Entity_Class ec = new Entity_Class(rs.getInt("_stt"), rs.getString("_classID"), rs.getString("_className"),
                            rs.getString("_staffID"), rs.getInt("_status"));
                    v.add(ec);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }

    public  Vector<Student> getStudentByClass(String classid) {
        Vector<Student> v = new Vector<Student>();
        String sql = "SELECT * FROM tblClassDetail JOIN tblStudent ON tblClassDetail._studentID = tblStudent._studentID WHERE _classID=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, classid);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    Student s = new Student(rs.getString("_studentID"), rs.getString("_emailName"), rs.getString("_firstName"),
                            rs.getString("_lastName"), rs.getString("_address"),
                            rs.getString("_phone"), rs.getDate("_birthDay"), rs.getString("_gender"));
                    v.add(s);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }

    public  Student getStudentByEmailName(String emailname) {
        Student v = null;
        String sql = "SELECT * FROM tblStudent WHERE _emailName=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, emailname);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    v = new Student(rs.getString("_studentID"), rs.getString("_emailName"), rs.getString("_firstName"),
                            rs.getString("_lastName"), rs.getString("_address"),
                            rs.getString("_phone"), rs.getDate("_birthDay"), rs.getString("_gender"));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }
      public  Vector<Student> getStudentByName(String classid,String name) {
        Vector<Student> v = new Vector<Student>();
        String sql = "SELECT * FROM tblClassDetail JOIN tblStudent ON tblClassDetail._studentID = tblStudent._studentID WHERE _classID=? AND _lastName=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, classid);
            prst.setString(2, name);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                   Student s  = new Student(rs.getString("_studentID"), rs.getString("_emailName"), rs.getString("_firstName"),
                            rs.getString("_lastName"), rs.getString("_address"),
                            rs.getString("_phone"), rs.getDate("_birthDay"), rs.getString("_gender"));
                   v.add(s);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }
    public  Vector<Assignment> getAssignmentByClass(String classid) {
        Vector<Assignment> v = new Vector<Assignment>();
        String sql = "SELECT * FROM tblAssignment WHERE _classID=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, classid);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    Assignment s = new Assignment(rs.getInt("_stt"), rs.getString("_assignmentID"), rs.getString("_assignmentName"),
                            rs.getString("_classID"), rs.getString("_disciplineName"),
                            rs.getDate("_startDate"), rs.getDate("_endDate"), rs.getInt("_status"));
                    v.add(s);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }

    public  Vector<SubmitOfAssignment> getSubmitOfAssignmentByAssignmentID(String assignmentid) {
        Vector<SubmitOfAssignment> v = new Vector<SubmitOfAssignment>();
        String sql = "SELECT * FROM tblSubmitOfAssignment JOIN tblStudent ON tblSubmitOfAssignment._studentID = tblStudent._studentID WHERE _assignmentID=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, assignmentid);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    SubmitOfAssignment s = new SubmitOfAssignment(rs.getInt("_stt"), rs.getString("_assignmentID"), rs.getString("_studentID"),
                            rs.getString("_emailName"), rs.getString("_firstName"), rs.getString("_lastName"),
                            rs.getDate("_postDay"), rs.getInt("_status"));
                    v.add(s);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }

    public  Vector<Mark> getMarkByAssignmentIDStaff(String assignmentid) {
        Vector<Mark> v = new Vector<Mark>();
        String sql = "SELECT tblMark._stt, tblMark._assignmentID, tblAssignment._disciplineName, tblMark._studentID,tblStudent._emailName, tblStudent._firstName,tblStudent._lastName ,tblMark._mark\n"
                + "FROM tblMark\n"
                + "JOIN tblAssignment ON tblMark._assignmentID = tblAssignment._assignmentID\n"
                + "JOIN tblStudent ON tblMark._studentID = tblStudent._studentID WHERE tblMark._assignmentID=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, assignmentid);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    Mark s = new Mark(rs.getInt("_stt"), rs.getString("_assignmentID"), rs.getString("_disciplineName"), rs.getString("_studentID"),
                            rs.getString("_emailName"), rs.getString("_firstName"), rs.getString("_lastName"), rs.getFloat("_mark"));
                    v.add(s);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }

    public  Vector<Mark> getMarkByStudentID(String studentid) {
        Vector<Mark> v = new Vector<Mark>();
        String sql = "SELECT * FROM tblMark JOIN tblAssignment ON tblMark._assignmentID = tblAssignment._assignmentID WHERE _studentID=?";
        try {
            PreparedStatement prst = getConnection().prepareStatement(sql);
            prst.setString(1, studentid);
            try (ResultSet rs = prst.executeQuery()) {
                while (rs.next()) {
                    Mark s = new Mark(rs.getInt("_stt"), rs.getString("_assignmentID"), rs.getString("_disciplineName"), rs.getFloat("_mark"));
                    v.add(s);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return v;
    }

    public  boolean enterMark(String assignmentid, String studentid, float mark) {
        int result = 0;
        String sql = "INSERT INTO tblMark VALUES(?,?,?)";
        try {
            try (PreparedStatement prst = getConnection().prepareStatement(sql)) {
                prst.setString(1, assignmentid);
                prst.setString(2, studentid);
                prst.setFloat(3, mark);
                result = prst.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result > 0;
    }
     public  boolean updateStatusAssignment(String assignmentid) {
        int result = 0;
        String sql = "UPDATE tblAssignment SET _status=1 WHERE _assignmentID=?";
        try {
            try (PreparedStatement prst = getConnection().prepareStatement(sql)) {
                prst.setString(1, assignmentid);
                result = prst.executeUpdate();
                prst.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result > 0;
    }
}
